package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.AlarmDto;
import com.hualude.roadsign.dto.DTRequestDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by xu_jiaHui on 2017/10/13.
 */
public class AlarmRepositoryImpl {
    @PersistenceContext
    private EntityManager em;

    public List<AlarmDto> getAlarm4DT(DTRequestDto dtRequestDto,Integer orgId,Integer actorId, Integer deviceType) throws Exception {
        String sql = "select orgName,AlarmId,DeviceId,DeviceState,DeviceOrgId,DeviceModel,DeviceManufacturer,DeviceAddress" +
                ",DeviceIpAddress,DevicePort,CreateUser,CreateTime,UpdateTime,DeviceType,DevicePathId,CycleTime,SpecialPoint " +
                "from tb_alarm left join tb_org on DeviceOrgId = OrgId ";
        String condition="";
        if(deviceType!=-2){
            condition+=" DeviceType =:deviceType ";
        }
        if(orgId!=-2){
            if(condition.length()>0)
                condition+=" and ";
            condition+=" DeviceOrgId in (:orgId) ";
        }
        if(condition.length()>0)
            sql+="where "+condition;
        sql+="order by CreateTime desc ";
        Query query = em.createNativeQuery(sql);
        if(deviceType!=-2){
            query.setParameter("deviceType",deviceType);
        }
        if(orgId!=-2){
          query.setParameter("orgId",orgId);
        }
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getAlarmCount4DT(Integer orgId,Integer actorId, Integer deviceType)throws Exception{
        String sql="select count(*) from tb_alarm  ";
        String condition="";
        if(deviceType!=-2){
            condition+=" DeviceType =:deviceType ";
        }
        if(orgId!=-2){
            if(condition.length()>0)
                condition+=" and ";
            condition+=" DeviceOrgId in (:orgId) ";
        }
        if(condition.length()>0)
            sql+=" where "+condition;
        sql+="";
        Query query = em.createNativeQuery(sql);
        if(deviceType!=-2){
            query.setParameter("deviceType",deviceType);
        }
        if(orgId!=-2){
            query.setParameter("orgId",orgId);
        }
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<AlarmDto> getAlarmPath(String deviceId) throws Exception {
        String sql = "select * from tb_alarm where DeviceId=" + deviceId;
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmDto.class));
        List<AlarmDto> alarmDtoList = query.getResultList();
        return alarmDtoList;
    }


    public List<AlarmDto> getAlarmBySearch4DT(DTRequestDto dtRequestDto, String orgId, String deviceId, String createTime, Integer deviceType) throws Exception {
        String sql = "select a.* ,o.orgName as orgName  from tb_alarm a left JOIN  tb_org o on  o.orgId=a.DeviceOrgId" +
                " where  a.DeviceId like '%" + deviceId + "%'";
        if(!orgId.contains("-2")){
            sql+=" and DeviceOrgId in (" + orgId + ") ";
        }
        sql+=" and a.createTime like '%" + createTime + "%' ";
        if (deviceType != null)
            sql += " and a.DeviceType =" + deviceType + " ";
        sql += " order by a.CreateTime desc ";
        //System.out.println(sql);
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();

    }

    public Integer getAlarmCountBySearch4DT(DTRequestDto dtRequestDto, String orgId, String deviceId, String createTime, Integer deviceType) throws Exception {
        String sql = "select count(*) from tb_alarm where DeviceId like '%" + deviceId + "%' ";
        if(!orgId.contains("-2")){
            sql+=" and DeviceOrgId in (" + orgId + ") ";
        }
        sql+=" and createTime like '%" + createTime + "%' ";
        if (deviceType != null)
            sql += " and DeviceType =" + deviceType + " ";
        sql += " order by CreateTime desc ";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());

    }

    public List<AlarmDto> getAlarmList(Integer orgId) throws Exception {
        String sql = "SELECT\n" +
                "tb_alarm.DeviceId,\n" +
                "tb_rule_group.ruleGroupName\n" +
                "FROM\n" +
                "tb_alarm\n" +
                "INNER JOIN tb_rule_group ON tb_alarm.DeviceModel = tb_rule_group.ruleGroupId\n" +
                "WHERE\n" +
                "tb_alarm.DevicePathId IS null and DeviceOrgId=" + orgId;
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(AlarmDto.class));
        List<AlarmDto> alarmDtoList = query.getResultList();
        return alarmDtoList;
    }

    public Integer updapteAlarmPath(Integer deviceType, Integer devicePathId, String deviceId) throws Exception {
        String sql = "update tb_alarm set DeviceType = '" + deviceType + "',DevicePathId = '" + devicePathId + "' where DeviceId ='"
                + deviceId + "'";
        Query query = em.createNativeQuery(sql);
        return query.executeUpdate();
    }
}
